<?php
/**
 * This file is part of the Agora distribution. 
 * Detailed copyright and licensing information can be found
 * in the gpl-3.0.txt file which should be included in the distribution.
 * 
 * @version		$Id: 3.0.14 Olympus 2010-09-28 rammstein4o $
 * @copyright  2007 - 2010 jVitals
 * @license   GPLv3 Open Source
 * @link       http://jvitals.com
 * @since      File available since initial release
 */
	defined ('IN_AGORA') or die;

	class CategoryModel extends Model
	{
		function __construct()
		{
			parent::__construct('categories');
		}

		function orderUp($cat_id)
		{
			$this->db->bind('id',$cat_id,'integer');
			$this->db->setQuery('SELECT disp_position FROM ##__categories WHERE id=:id');
			$old_pos = $this->db->loadResult();

			$this->db->bind('order',$old_pos,'integer');
			$this->db->setQuery('SELECT id FROM ##__categories WHERE disp_position < :order ORDER BY disp_position DESC LIMIT 1');
			$next_cat = $this->db->loadResult();
			if (!is_null($next_cat)) {
				$this->db->bind('next_cat',$next_cat,'integer');
				$this->db->setQuery('UPDATE ##__categories SET disp_position = disp_position + 1 WHERE  id = :next_cat');
				$this->db->query();
			}

			$this->db->bind('id',$cat_id,'integer');
			$this->db->setQuery('UPDATE ##__categories SET disp_position = disp_position - 1 WHERE id=:id');
			$this->db->query();
			$this->reorder();
		}

		function orderDown($cat_id)
		{
			$this->db->bind('id',$cat_id,'integer');
			$this->db->setQuery('SELECT disp_position FROM ##__categories WHERE id=:id');
			$old_pos = $this->db->loadResult();

			$this->db->bind('order',$old_pos,'integer');
			$this->db->setQuery('SELECT id FROM ##__categories WHERE disp_position > :order ORDER BY disp_position ASC LIMIT 1');
			$next_cat = $this->db->loadResult();
			if (!is_null($next_cat)) {
				$this->db->bind('next_cat',$next_cat,'integer');
				$this->db->setQuery('UPDATE ##__categories SET disp_position = disp_position - 1 WHERE  id = :next_cat');
				$this->db->query();
			}

			$this->db->bind('id',$cat_id,'integer');
			$this->db->setQuery('UPDATE ##__categories SET disp_position = disp_position + 1 WHERE id=:id');
			$this->db->query();
			$this->reorder();
		}

		function reorder()
		{
			$this->db->setQuery('SELECT id, disp_position FROM ##__categories ORDER BY disp_position, id');
			$cats = $this->db->loadAssocList();
			$n = 1;
			foreach ($cats as $cat) {
				if ($cat['disp_position'] != $n) {
					$this->db->bind('order',$n,'integer');
					$this->db->bind('id',$cat['id'],'integer');
					$this->db->setQuery('UPDATE ##__categories SET disp_position = :order WHERE id=:id');
					$this->db->query();
				}
				$n += 1;
			}
		}

		function setOrder($cat_id, $disp_position)
		{
			$this->db->bind('id',$cat_id,'integer');
			$this->db->bind('order',$disp_position,'integer');
			$this->db->setQuery('UPDATE ##__categories SET disp_position=:order WHERE id=:id');
			$this->db->query();
		}

		function loadForums($catId)
		{
			$this->db->setQuery("SELECT * FROM {$this->prefix}forums".
				' WHERE cat_id = '.$this->db->Quote($catId).
				" AND (parent_forum_id IS NULL OR parent_forum_id = 0) ORDER BY disp_position ASC");

			$forums = $this->db->loadAssocList();
			foreach ($forums as $i => $forum)
			{
				$forum_id = $this->db->Quote($forum['id']);
				$sql = "SELECT post.*,topic.subject, topic.id as topic_id, topic.num_replies as num_posts FROM {$this->prefix}posts AS post" .
						" INNER JOIN {$this->prefix}topics AS topic".
						" 	ON post.topic_id = topic.id".
						" WHERE post.approved = 1 AND topic.forum_id = $forum_id".
						" ORDER BY post.posted DESC LIMIT 1";

				$this->db->setQuery($sql);
				$post = $this->db->loadAssoc();
/*				print $sql;
				print_r($post);*/
				$forums[$i]['last_post'] = $post['posted'];
				$forums[$i]['last_post_id'] = $post['id'];
				$forums[$i]['last_poster'] = $post['poster'];
				$forums[$i]['last_poster_id'] = $post['poster_id'];
				$forums[$i]['last_post_topic_id'] = $post['topic_id'];
				$forums[$i]['num_posts'] = $post['num_posts'];
				$forums[$i]['subject'] = $post['subject'];

				$this->db->setQuery("SELECT COUNT(*) FROM {$this->prefix}forums WHERE parent_forum_id = $forum_id");
				$forums[$i]['subforum_count'] = $this->db->loadResult();
			}

			return $forums;
		}

		function loadQuickJump()
		{
			$sql = 'SELECT cat.cat_name,forum.id, forum.forum_name FROM ##__categories AS cat INNER JOIN ##__forums AS forum ON cat.id = forum.cat_id';
			$this->db->setQuery($sql);
			if (!$this->db->query()) {
				print $this->db->getErrorMsg();
				die;
			}
			$jump = array();
			foreach ($this->db->loadRowList() as $forum) {
				$jump[$forum[0]][$forum[1]] = $forum[2];
			}

			return $jump;
		}	
		
		function loadAll($key = '')
		{
			parent::setOrder("ORDER BY disp_position ASC");
			return Model::loadAll($key);
		}
		
		function moveUp($id)
		{
			$this->db->setQuery('SELECT disp_position  FROM ##__categories WHERE id ='. intval($id));
			$old_disp = $this->db->loadResult();
			
			$this->db->setQuery("SELECT MIN(disp_position) FROM ##__categories");
			$min = $this->db->loadResult();
			if ($old_disp <= $min) return;
			
			$this->db->setQuery("UPDATE ##__categories SET disp_position = disp_position + 1 WHERE disp_position < $old_disp ORDER BY disp_position DESC LIMIT 1");
			$this->db->query();
			
			$this->db->setQuery('UPDATE ##__categories SET disp_position = disp_position - 1 WHERE id = '.intval($id));
			$this->db->query();
		}
		
		function getIconSyleData($id)
		{
            $this->db->setQuery('SELECT * FROM ##__icon_styles WHERE id ='.intval($id));
			return $this->db->loadAssoc();		    
		}
		
		function moveDown($id)
		{
			$this->db->setQuery('SELECT disp_position FROM ##__categories WHERE id ='.intval($id));
			$old_disp = $this->db->loadResult();
			
			$this->db->setQuery("SELECT MAX(disp_position) FROM ##__categories");
			$max = $this->db->loadResult();
			if ($old_disp >= $max) return;
			
			$this->db->setQuery("UPDATE ##__categories SET disp_position = disp_position - 1 WHERE disp_position > $old_disp ORDER BY disp_position ASC LIMIT 1");
			$this->db->query();
			
			$this->db->setQuery('UPDATE ##__categories SET disp_position = disp_position + 1 WHERE id = '.intval($id));
			$this->db->query();
		}
		
		function add($name, $order)
		{
/*			$this->setQuery('SELECT COALESCE(MAX(disp_position) + 1,1) FROM ##__categories');
			$pos = $this->db->loadResult();*/
			//$name = $this->db->Quote($name);
			$pos = intval($order) ;

			$this->setQuery('UPDATE ##__categories SET disp_position = disp_position + 1 WHERE disp_position > '.$pos);
			$this->db->query();
			return parent::add($name);
			//$this->setQuery('INSERT INTO ##__categories (cat_name,disp_position) VALUES ('.$name.','.($pos+1).') ');
    		//$this->db->query();
		}
	
		function delete($id)
		{
			$this->db->bind('id',$id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums WHERE cat_id = :id');
			$forums = $this->db->loadResultArray();

			$forum_model = & Model::getInstance('ForumModel');
			foreach ($forums as $forum) {
				$forum_model->delete($forum);
			}
			parent::delete($id);
		}
		
		function moveForumsTo($from_id, $to_id)
		{
		    $from_id = (int)$from_id;
		    $to_id = (int)$to_id;
		    if($to_id == $from_id) return false;		    
		    
			$this->db->bind('id',$from_id,'integer');
			$this->db->setQuery('SELECT id FROM ##__forums WHERE cat_id = :id');
			$forums = $this->db->loadResultArray();
			
			foreach ($forums as $id) {
			    $this->db->bind('toid',$to_id,'integer');
				$this->db->setQuery('UPDATE ##__forums SET cat_id = :toid WHERE id ='.$id);
				$this->db->query();
			}
			parent::delete($from_id);
		}
	
		function disable($id)
		{
			$id = intval($id);
			$this->setQuery("UPDATE ##__categories SET enable = 0 WHERE id = '$id'");
			$this->db->query();
		}
		
		function enable($id)
		{
			$id = intval($id);
			$this->setQuery("UPDATE ##__categories SET enable = 1 WHERE id = '$id'");
			$this->db->query();
		}
	}
?>